package com.pks.autoInsurance.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.pks.autoInsurance.dto.RegistrationDto;

/**
 * Implementation class for RegistrationDAO
 * 
 * @author Pankaj Soni
 */

public class RegistrationDAOImpl extends AutoInsuranceDAO implements
		RegistrationDAO {
	/**
	 * 
	 * @see com.cts.autoInsurance.dao.RegistrationDAO#createUser(com.cts.autoInsurance
	 *      .dto.PersonalDetailsDto)
	 */
	@Override
	public boolean createUser(RegistrationDto registrationDtoReq) {
		System.out.println(getClass() + "::::createUser::::begin");
		Connection connection = getConnection();
		String sqlRegistration = "INSERT INTO REGISTRATION"
				+ " VALUES(?,?,?,?,?,?,?,?,?,?,?)";
		String sqlVehicle = "INSERT INTO VEHICLE(SSN,TYPE,MODEL,MAKE,"
				+ "REGNO,POLICY_TYPE,POLICY_AMOUNT,MF_YEAR,TOTAL_ACCIDENT)"
				+ " VALUES(?,'0','NA','NA','NA','NA',0,'1900',0)";

		try {
			connection.setAutoCommit(false);
			// insert value in registration table
			PreparedStatement preparedStatement = connection
					.prepareStatement(sqlRegistration);
			java.sql.Date dateofBirth = new java.sql.Date(registrationDtoReq
					.getDateOfBirth().getTime());

			preparedStatement.setString(1, registrationDtoReq.getSsn());
			preparedStatement.setString(2, registrationDtoReq.getPasswd());
			preparedStatement.setString(3, registrationDtoReq.getFirstName());
			preparedStatement.setString(4, registrationDtoReq.getLastName());
			preparedStatement.setString(5, registrationDtoReq.getGender());
			preparedStatement.setDate(6, dateofBirth);
			preparedStatement.setString(7, registrationDtoReq.getMobileNo());
			preparedStatement.setString(8, registrationDtoReq.getEmail());
			preparedStatement.setString(9, registrationDtoReq.getCity());
			preparedStatement.setString(10, registrationDtoReq.getBloodGroup());
			preparedStatement.setString(11,
					registrationDtoReq.getDrivingLicence());

			int registrationRowInserted = preparedStatement.executeUpdate();

			// insert into vehicle
			preparedStatement = connection.prepareStatement(sqlVehicle);
			preparedStatement.setString(1, registrationDtoReq.getSsn());
			int vehicleRowInserted = preparedStatement.executeUpdate();
			System.out.println(":::registrationRowInserted:::"
					+ registrationRowInserted);
			System.out.println(":::vehicleRowInserted:::" + vehicleRowInserted);

		} catch (SQLException e) {
			System.err.println(getClass() + "::::" + e.getMessage());
			try {
				connection.rollback();
			} catch (SQLException ex) {
				System.err.println(getClass() + "::::" + ex.getMessage());
				return false;
			}
		} finally {
			try {
				connection.commit();
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
		}
		System.out.println(getClass() + "::::createUser::::end");
		return true;
	}

	/**
	 * 
	 * @see com.cts.autoInsurance.dao.RegistrationDAO#isExistingUser(com.cts.
	 *      autoInsurance.dto.LoginDto)
	 */
	@Override
	public boolean isExistingUser(RegistrationDto registrationDtoReq) {
		System.out.println(getClass() + "::::isExistingUser::::begin");
		Connection connection = getConnection();
		String sql = "SELECT * FROM REGISTRATION WHERE SSN=?";
		boolean userExists = false;
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, registrationDtoReq.getSsn());
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				userExists = true;
			}
		} catch (SQLException e) {
			System.err.println(getClass() + "::::" + e.getMessage());
			userExists = false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
		}
		System.out.println("::::isExistingUser::::" + userExists);
		System.out.println(getClass() + "::::isExistingUser::::end");
		return userExists;
	}

	/**
	 * @see com.pks.autoInsurance.dao.RegistrationDAO#updateUser(com.pks.autoInsurance.dto.RegistrationDto)
	 */
	@Override
	public boolean updateUser(RegistrationDto registrationDtoReq) {
		System.out.println(getClass() + "::::updateUser::::begin");
		Connection connection = getConnection();
		String sql = "UPDATE REGISTRATION "
				+ "SET PASSWD=?,FNAME=?,LNAME=?,GENDER=?,MOBILENO=?,"
				+ "EMAIL=?,CITY=?,BLOOD_GROUP=?,DRIVING_LICENSE=?,"
				+ "DATE_OF_BIRTH=? WHERE SSN=?";

		try {
			connection.setAutoCommit(false);
			// update registration table
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			java.sql.Date dateofBirth = new java.sql.Date(registrationDtoReq
					.getDateOfBirth().getTime());
			preparedStatement.setString(1, registrationDtoReq.getPasswd());
			preparedStatement.setString(2, registrationDtoReq.getFirstName());
			preparedStatement.setString(3, registrationDtoReq.getLastName());
			preparedStatement.setString(4, registrationDtoReq.getGender());
			preparedStatement.setString(5, registrationDtoReq.getMobileNo());
			preparedStatement.setString(6, registrationDtoReq.getEmail());
			preparedStatement.setString(7, registrationDtoReq.getCity());
			preparedStatement.setString(8, registrationDtoReq.getBloodGroup());
			preparedStatement.setString(9,
					registrationDtoReq.getDrivingLicence());
			preparedStatement.setDate(10, dateofBirth);
			preparedStatement.setString(11, registrationDtoReq.getSsn());
			int userProfileUpdated = preparedStatement.executeUpdate();
			System.out.println(getClass() + ":::userProfileUpdated:::"
					+ userProfileUpdated);

		} catch (SQLException e) {
			System.err.println(getClass() + "::::" + e.getMessage());
			try {
				connection.rollback();
			} catch (SQLException ex) {
				System.err.println(getClass() + "::::" + ex.getMessage());
				return false;
			}
		} finally {
			try {
				connection.commit();
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
		}
		System.out.println(getClass() + "::::updateUser::::end");
		return true;
	}

	/**
	 * @see com.pks.autoInsurance.dao.RegistrationDAO#getUserProfile(java.lang.String)
	 */
	@Override
	public RegistrationDto getUserProfile(String ssn) {
		System.out.println(getClass() + "::::getUserProfile::::begin");
		Connection connection = getConnection();
		String sql = "SELECT SSN,PASSWD,FNAME,LNAME,GENDER,DATE_OF_BIRTH,"
				+ "MOBILENO,EMAIL,CITY,BLOOD_GROUP,DRIVING_LICENSE"
				+ " FROM REGISTRATION WHERE SSN=?";
		RegistrationDto registrationDto = new RegistrationDto();
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, ssn);
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				registrationDto.setSsn(resultSet.getString("SSN"));
				registrationDto.setPasswd(resultSet.getString("PASSWD"));
				registrationDto.setFirstName(resultSet.getString("FNAME"));
				registrationDto.setLastName(resultSet.getString("LNAME"));
				registrationDto.setGender(resultSet.getString("GENDER"));
				registrationDto.setDateOfBirth(resultSet
						.getDate("DATE_OF_BIRTH"));
				registrationDto.setMobileNo(resultSet.getString("MOBILENO"));
				registrationDto.setEmail(resultSet.getString("EMAIL"));
				registrationDto.setCity(resultSet.getString("CITY"));
				registrationDto.setBloodGroup(resultSet
						.getString("BLOOD_GROUP"));
				registrationDto.setDrivingLicence(resultSet
						.getString("DRIVING_LICENSE"));
			}
			return registrationDto;
		} catch (SQLException e) {
			System.err.println(getClass() + "::::" + e.getMessage());
			return null;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
			System.out.println(getClass() + "::::getUserProfile::::end");
		}
	}

	/**
	 * @see com.pks.autoInsurance.dao.RegistrationDAO#getUsersList()
	 */
	@Override
	public List<RegistrationDto> getUsersList() {
		System.out.println(getClass() + "::::getUsersList::::begin");
		Connection connection = getConnection();
		List<RegistrationDto> personList = new ArrayList<RegistrationDto>();
		RegistrationDto registrationDto;
		String sql = "SELECT SSN,FNAME,LNAME,MOBILENO,CITY,EMAIL,"
				+ "DATE_OF_BIRTH,GENDER FROM REGISTRATION";
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				registrationDto = new RegistrationDto();
				registrationDto.setSsn(resultSet.getString("SSN"));
				registrationDto.setFirstName(resultSet.getString("FNAME"));
				registrationDto.setLastName(resultSet.getString("LNAME"));
				registrationDto.setMobileNo(resultSet.getString("MOBILENO"));
				registrationDto.setCity(resultSet.getString("CITY"));
				registrationDto.setEmail(resultSet.getString("EMAIL"));
				registrationDto.setDateOfBirth(resultSet
						.getDate("DATE_OF_BIRTH"));
				registrationDto.setGender(resultSet.getString("GENDER"));
				personList.add(registrationDto);
			}

			return personList;

		} catch (SQLException e) {
			System.err.println(getClass() + ":::getUsersList() SQLException"
					+ e.getMessage());
			return null;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
			System.out.println(getClass() + "::::getUsersList::::end");
		}
	}

	/**
	 * @see com.pks.autoInsurance.dao.RegistrationDAO#getUsersList(java.lang.String)
	 */
	@Override
	public List<RegistrationDto> getUsersList(String ssn) {
		System.out.println(getClass() + "::::getUsersList::::begin");
		System.out.println("ssn::::" + ssn);
		Connection connection = getConnection();
		List<RegistrationDto> registrationList = new ArrayList<RegistrationDto>();
		RegistrationDto registrationDto;
		String sql = "SELECT SSN,FNAME,LNAME,MOBILENO,CITY,EMAIL,DATE_OF_BIRTH,GENDER FROM REGISTRATION WHERE SSN LIKE ?";
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, "%" + ssn + "%");
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				registrationDto = new RegistrationDto();
				registrationDto.setSsn(resultSet.getString("SSN"));
				registrationDto.setFirstName(resultSet.getString("FNAME"));
				registrationDto.setLastName(resultSet.getString("LNAME"));
				registrationDto.setMobileNo(resultSet.getString("MOBILENO"));
				registrationDto.setCity(resultSet.getString("CITY"));
				registrationDto.setEmail(resultSet.getString("EMAIL"));
				registrationDto.setDateOfBirth(resultSet
						.getDate("DATE_OF_BIRTH"));
				registrationDto.setGender(resultSet.getString("GENDER"));
				registrationList.add(registrationDto);
			}
			System.out.println("List retrived from system::::"
					+ registrationList);
			return registrationList;

		} catch (SQLException e) {
			System.err.println(getClass() + ":::getUsersList() SQLException"
					+ e.getMessage());
			return null;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
			System.out.println(getClass() + "::::getUsersList::::end");
		}
	}

	/**
	 * @see com.pks.autoInsurance.dao.RegistrationDAO#deleteUser(java.lang.String)
	 */
	@Override
	public int deleteUser(String ssn) {
		System.out.println(getClass() + "::::deleteUser::::begin");
		Connection connection = getConnection();
		String sqlRegistrationDelete = "DELETE FROM REGISTRATION WHERE SSN=?";
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sqlRegistrationDelete);
			preparedStatement.setString(1, ssn);
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				System.err.println(getClass() + "::::" + e1.getMessage());
				e.printStackTrace();
			}
			System.err.println(getClass() + "::::" + e.getMessage());
		} finally {
			try {
				connection.commit();
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
			System.out.println(getClass() + "::::deleteUser::::end");
		}
		return 0;
	}

}
